{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/peremartra/Large-Language-Model-Notebooks-Course/blob/main/P1-NL2SQL/6_3_AWS_Bedrock_NL2SQL_Client.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Paa52inqag9w"
      },
      "source": [
        "<div>\n",
        "    <h1>Large Language Models Projects</a></h1>\n",
        "    <h3>Apply and Implement Strategies for Large Language Models</h3>\n",
        "    <h2>6.3-Calling AWS Bedrock from Python. </h2>\n",
        "    <h3></h3>\n",
        "</div>\n",
        "\n",
        "by [Pere Martra](https://www.linkedin.com/in/pere-martra/)\n",
        "__________\n",
        "Models: llama3-8b-instruct-v1\n",
        "\n",
        "Colab Environment: CPU\n",
        "\n",
        "Keys:\n",
        "* BedRock\n",
        "* AWS\n",
        "* NL2SQL\n",
        "\n",
        "__________"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uxFVaxlskUf-"
      },
      "source": [
        "In this notebook, we make a call to a Model from AWS Bedrock  that we've set up to work as a translator for SQL queries from natural language.\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "PQmhudeOj9PJ",
        "outputId": "a4d6a38c-90fb-446a-c0bb-c6a2695f8ff5"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Note: you may need to restart the kernel to use updated packages.\n"
          ]
        }
      ],
      "source": [
        "pip install -q boto3==1.34.108"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Jssm3zDHbKq-"
      },
      "outputs": [],
      "source": [
        "import boto3\n",
        "import json\n",
        "from getpass import getpass"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "UeZIlGHbkfHL",
        "outputId": "d7b591a7-a462-4b2f-cce2-70ca0e30e9c4"
      },
      "outputs": [
        {
          "name": "stdin",
          "output_type": "stream",
          "text": [
            "AWS Acces key:  ········\n"
          ]
        }
      ],
      "source": [
        "aws_access_key_id = getpass('AWS Acces key: ')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LjHrlQ3IkfVw",
        "outputId": "15023f96-056a-495a-8049-3a8d101578a4"
      },
      "outputs": [
        {
          "name": "stdin",
          "output_type": "stream",
          "text": [
            "AWS Secret Key:  ········\n"
          ]
        }
      ],
      "source": [
        "aws_secret_access_key = getpass('AWS Secret Key: ')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "B2C_0LNjHFo3"
      },
      "outputs": [],
      "source": [
        "client = boto3.client(\"bedrock-runtime\",\n",
        "                      region_name=\"us-west-2\",\n",
        "                      aws_access_key_id = aws_access_key_id,\n",
        "                      aws_secret_access_key= aws_secret_access_key)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Ua4SgAVYwrVF"
      },
      "outputs": [],
      "source": [
        "# Set the model ID, e.g., Llama 3 8B Instruct.\n",
        "model_id = \"meta.llama3-8b-instruct-v1:0\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "YMVK994yhbnP"
      },
      "outputs": [],
      "source": [
        "# Define the user message to send.\n",
        "user_message = \"What is the name of the best paid employee?\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "BW89l6qdPEUy"
      },
      "outputs": [],
      "source": [
        "model_instructions = \"\"\"\n",
        "Your task is to convert a question into a SQL query, given a SQL database schema.\n",
        "Adhere to these rules:\n",
        "- **Deliberately go through the question and database schema word by word to appropriately answer the question.\n",
        "- **Return Only SQL Code.\n",
        "   ### Input\n",
        "   Generate a SQL query that answers the question below.\n",
        "   This query will run on a database whose schema is represented in this string:\n",
        "\n",
        "   create table employees(\n",
        "       ID_Usr INT primary key,-- Unique Id for employee\n",
        "       name VARCHAR -- Name of employee\n",
        "       );\n",
        "\n",
        "   create table salary(\n",
        "       ID_Usr INT,-- Unique Id for employee\n",
        "       year DATE, -- Date\n",
        "       salary FLOAT, --Salary of employee\n",
        "       foreign key (ID_Usr) references employees(ID_Usr) -- Join Employees with salary\n",
        "       );\n",
        "\n",
        "   create table studies(\n",
        "       ID_study INT, -- Unique ID study\n",
        "       ID_Usr INT, -- ID employee\n",
        "       educational_level INT,  -- 5=phd, 4=Master, 3=Bachelor\n",
        "       Institution VARCHAR, --Name of instituon where eployee studied\n",
        "       Years DATE, -- Date acomplishement stdy\n",
        "       Speciality VARCHAR, -- Speciality of studies\n",
        "       primary key (ID_study, ID_Usr), --Primary Key ID_Usr + ID_Study\n",
        "       foreign key(ID_Usr) references employees (ID_Usr)\n",
        "       );\n",
        "\n",
        "\"\"\"\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "9yc2edu10CbQ"
      },
      "outputs": [],
      "source": [
        "# Embed the message in Llama 3's prompt format.\n",
        "prompt = f\"\"\"\n",
        "<|begin_of_text|>\n",
        "<|start_header_id|>system<|end_header_id|>\n",
        "{model_instructions}\n",
        "<|eot_id|>\n",
        "<|start_header_id|>user<|end_header_id|>\n",
        "{user_message}\n",
        "<|eot_id|>\n",
        "<|start_header_id|>assistant<|end_header_id|>\n",
        "\"\"\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "RE5bDGz8PEUy",
        "outputId": "9ecd694b-c1b7-4217-9c3f-58aae49dd7e6"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "\n",
            "<|begin_of_text|>\n",
            "<|start_header_id|>system<|end_header_id|>\n",
            "\n",
            "Your task is to convert a question into a SQL query, given a SQL database schema.\n",
            "Adhere to these rules:\n",
            "- **Deliberately go through the question and database schema word by word to appropriately answer the question.\n",
            "- **Return Only SQL Code. \n",
            "   ### Input\n",
            "   Generate a SQL query that answers the question below.\n",
            "   This query will run on a database whose schema is represented in this string:\n",
            "\n",
            "   create table employees(\n",
            "       ID_Usr INT primary key,-- Unique Id for employee\n",
            "       name VARCHAR -- Name of employee\n",
            "       );\n",
            "\n",
            "   create table salary(\n",
            "       ID_Usr INT,-- Unique Id for employee\n",
            "       year DATE, -- Date\n",
            "       salary FLOAT, --Salary of employee\n",
            "       foreign key (ID_Usr) references employees(ID_Usr) -- Join Employees with salary\n",
            "       );\n",
            "\n",
            "   create table studies(\n",
            "       ID_study INT, -- Unique ID study\n",
            "       ID_Usr INT, -- ID employee\n",
            "       educational_level INT,  -- 5=phd, 4=Master, 3=Bachelor\n",
            "       Institution VARCHAR, --Name of instituon where eployee studied\n",
            "       Years DATE, -- Date acomplishement stdy\n",
            "       Speciality VARCHAR, -- Speciality of studies\n",
            "       primary key (ID_study, ID_Usr), --Primary Key ID_Usr + ID_Study\n",
            "       foreign key(ID_Usr) references employees (ID_Usr)\n",
            "       );\n",
            "\n",
            "\n",
            "<|eot_id|>\n",
            "<|start_header_id|>user<|end_header_id|>\n",
            "What is the name of the best paid employee?\n",
            "<|eot_id|>\n",
            "<|start_header_id|>assistant<|end_header_id|>\n",
            "\n"
          ]
        }
      ],
      "source": [
        "print (prompt)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "tKCmd3S90KET"
      },
      "outputs": [],
      "source": [
        "# Format the request payload using the model's native structure.\n",
        "hyper = {\n",
        "    \"prompt\": prompt,\n",
        "    # Optional inference parameters:\n",
        "    \"max_gen_len\": 512,\n",
        "    \"temperature\": 0.0\n",
        "}"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xnkNtdbA0Lv2"
      },
      "outputs": [],
      "source": [
        "# Encode and send the request.\n",
        "response = client.invoke_model(body=json.dumps(hyper), modelId=model_id)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "CZfzGp6e0Pfd"
      },
      "outputs": [],
      "source": [
        "# Decode the native response body.\n",
        "model_response = json.loads(response[\"body\"].read())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_HAm1LgD1-af",
        "outputId": "44731d22-5e8e-466d-a245-c602ede1bcd9"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Here is the SQL query that answers the question:\n",
            "\n",
            "```sql\n",
            "SELECT e.name\n",
            "FROM employees e\n",
            "JOIN salary s ON e.ID_Usr = s.ID_Usr\n",
            "ORDER BY s.salary DESC\n",
            "LIMIT 1;\n",
            "```\n"
          ]
        }
      ],
      "source": [
        "# Extract and print the generated text.\n",
        "response_text = model_response[\"generation\"]\n",
        "print(response_text)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "tARgaG9l2AG8"
      },
      "outputs": [],
      "source": []
    }
  ],
  "metadata": {
    "colab": {
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "display_name": "Python 3 (ipykernel)",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.10.14"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}